package com.aseo.business.hibernate;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Set;

import javax.annotation.Resource;
import javax.validation.ConstraintViolation;

import org.springframework.stereotype.Service;

import com.aseo.exception.ConstraintViolationException;
import com.aseo.model.dao.Dao;
import com.aseo.model.entity.hibernate.Reclamo;
import com.aseo.util.Estado;

@Service(value = "com.aseo.business.hibernate.ReclamoManager")
public class ReclamoManager implements ManagerInterface<Reclamo> {

	@Resource
	private Dao dao;

	@Override
	public List<Reclamo> getAll() {
		final List<Reclamo> list = dao.find(Reclamo.class);
		return list;
	}

	@Override
	public void persist(Reclamo t) {
		dao.persist(t);
	}

	public List<Reclamo> getReclamosCandidatos(Reclamo reclamo) {

		final List<Reclamo> list = dao
				.find(" SELECT reclamo FROM com.aseo.model.entity.hibernate.Reclamo reclamo "
						+ " WHERE (reclamo.reclamoIdPadre.reclamoId = "
						+ reclamo.getReclamoId()
						+ " OR ( reclamo.reclamoIdPadre IS NULL "
						+ " AND NOT reclamo IN "
						+ " 		(SELECT reclamo_hijo.reclamoIdPadre "
						+ "		FROM com.aseo.model.entity.hibernate.Reclamo reclamo_hijo "
						+ "		WHERE NOT reclamo_hijo.reclamoIdPadre IS NULL)))"
						+ " AND  reclamo.domicilioId.sectorId.sectorId = "
						+ reclamo.getDomicilioId().getSectorId().getSectorId()
						+ " AND  reclamo.reclamoId <> "
						+ reclamo.getReclamoId()
						+ " ORDER BY reclamo.reclamoId");
		return list;
	}

	public List<Reclamo> getReclamosPuntoRecoleccion(Reclamo reclamo) {

		final List<Reclamo> list = dao
				.find(" SELECT reclamo FROM com.aseo.model.entity.hibernate.Reclamo reclamo "
						+ " WHERE reclamo.reclamoIdPadre IS NULL "
						+ " AND NOT reclamo IN "
						+ " 		(SELECT reclamo_hijo.reclamoIdPadre "
						+ "		FROM com.aseo.model.entity.hibernate.Reclamo reclamo_hijo "
						+ "		WHERE NOT reclamo_hijo.reclamoIdPadre IS NULL)"
						+ " AND  reclamo.puntoRecoleccionId.puntoRecoleccionId = "
						+ reclamo.getPuntoRecoleccionId()
								.getPuntoRecoleccionId() +

						" ORDER BY reclamo.reclamoId");
		return list;
	}

	public List<Reclamo> getReclamosNoCerrados() {
		final List<Reclamo> list = dao
				.find(" SELECT reclamo FROM com.aseo.model.entity.hibernate.Reclamo reclamo "
						+ " WHERE reclamo.estado IN ("
						+ Estado.RECLAMO_ACTIVO
						+ ","
						+ Estado.RECLAMO_EN_PROCESO
						+ ")"
						+ " AND reclamo.reclamoIdPadre IS NULL "
						+ " ORDER BY reclamo.fechaReclamo DESC");
		return list;
	}

	public List<Reclamo> getReclamosNoCerradosNoPadre() {
		final List<Reclamo> list = dao
				.find(" SELECT reclamo FROM com.aseo.model.entity.hibernate.Reclamo reclamo "
						+ " WHERE reclamo.estado IN ("
						+ Estado.RECLAMO_ACTIVO
						+ ","
						+ Estado.RECLAMO_EN_PROCESO
						+ ")"
						+ " AND reclamo.reclamoIdPadre IS NULL "
						+ " AND NOT reclamo IN "
						+ "		(SELECT reclamo_hijo.reclamoIdPadre "
						+ "  		FROM com.aseo.model.entity.hibernate.Reclamo reclamo_hijo "
						+ " 		WHERE NOT reclamo_hijo.reclamoIdPadre IS NULL) "
						+ " ORDER BY reclamo.fechaReclamo DESC");
		return list;
	}

	public List<Reclamo> getReclamosPadres() {
		final List<Reclamo> list = dao.find(" SELECT reclamo "
				+ " FROM com.aseo.model.entity.hibernate.Reclamo reclamo "
				+ " WHERE reclamo.reclamoIdPadre IS NULL "
				+ " ORDER BY reclamo.fechaReclamo DESC");
		return list;
	}

	public Reclamo getReclamoByCodigo(String codigo) {
		final List<Reclamo> list = dao
				.find("Select reclamo from com.aseo.model.entity.hibernate.Reclamo reclamo where reclamo.codigo = '"
						+ codigo + "'");
		return (list.size() == 0) ? null : list.get(0);

	}

	public List<Reclamo> getReclamos(Long nacionalidad, Long puntoRecoleccion,
			String identificador, Date fecha, String reclamador) {
		String sql = "SELECT reclamo FROM com.aseo.model.entity.hibernate.Reclamo reclamo ";
		String where = "";
		if (puntoRecoleccion != null) {
			where += "reclamo.puntoRecoleccionId.puntoRecoleccionId   = "
					+ puntoRecoleccion;
		}
		if (fecha != null) {
			where += ((where.equals("")) ? "" : " AND ")
					+ "date (reclamo.fechaReclamo)   = '"
					+ SimpleDateFormat.getDateInstance(DateFormat.MEDIUM)
							.format(fecha) + "'";
		}
		if (reclamador != null && !reclamador.equals("")) {
			where += ((where.equals("")) ? "" : " AND ")
					+ "  reclamo.reclamadorId.reclamadorId  in  "
					+ "(select reclamador.reclamadorId from com.aseo.model.entity.hibernate.Reclamador reclamador "
					+ " where UPPER (reclamador.nombres) like '%" + reclamador
					+ "%' )  ";

		}
		if (identificador != null && !identificador.equals("")) {
			where += ((where.equals("")) ? "" : " AND ")
					+ " reclamo.reclamadorId.reclamadorId in "
					+ " (select reclamador.reclamadorId from com.aseo.model.entity.hibernate.Reclamador reclamador "
					+ " where reclamador.tipoIdentificacionId.tipoIdentificacionId = "
					+ nacionalidad + " and reclamador.numeroIdentificador = '"
					+ identificador + "' )  ";
		}

		sql = (where.equals("")) ? sql : sql + " WHERE " + where;
		System.out.println("SQL: " + sql);
		final List<Reclamo> list = dao.find(sql);
		return list;
	}

	public void validate(Reclamo reclamo) throws ConstraintViolationException {

		Set<ConstraintViolation<Reclamo>> constraintViolations = dao
				.validate(reclamo);

		for (ConstraintViolation<Reclamo> constraintViolation : constraintViolations) {
			throw new ConstraintViolationException(constraintViolation
					.getMessage());
		}

	}

}
